﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Microsoft.SharePoint;

namespace JobCreateInDocs3
{

    public static class DBSuplier
    {

        #region Members
        static Ko2.Portal.Env _env = new Ko2.Portal.Env();
        private static string _ConnStr = "";
        public static string ConnStr
        {
            get
            {
                _ConnStr = _env.GetAppSetting("AXAPTAConnect");
                return DBSuplier._ConnStr;
            }
        }
        #endregion Members
        #region SQL
        private static string _sql_GetDataByDateTime =
@"
SELECT
NAMEALIAS AS NAME
, ACCOUNTNUM AS ID
, NAME AS FullName
, VENDACCOUNTCOGNOS
, MODIFIEDDATE
, MODIFIEDTIME
FROM
dbo.v_khim_VENDTABLE AS w
WHERE
(MODIFIEDDATE >= ?) AND (MODIFIEDTIME >= ?)
OR (MODIFIEDDATE >= ?)
ORDER BY FullName
"
;
        #endregion SQL
        #region Data
        public static Exception GetDataByDataTime(ref DataTable tblRet, DateTime dtFrom)
        {
            Exception ret = null;
            string connstr = ConnStr;
            if (tblRet == null)
                tblRet = new DataTable();
            DateTime d = new DateTime(dtFrom.Year, dtFrom.Month, dtFrom.Day);
            DateTime d2 = d.AddDays(1);
            TimeSpan t = dtFrom.TimeOfDay;
            int seconds = 0;
            if (t.TotalSeconds > 0)
                seconds = Convert.ToInt32(t.TotalSeconds - 1);
            using (OleDbConnection conn = new OleDbConnection(connstr))
            {
                try
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(_sql_GetDataByDateTime, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 6000;
                        cmd.Parameters.Add("ModifiedDate", DbType.DateTime).Value = d;
                        cmd.Parameters.Add("ModifiedTime", DbType.Int32).Value = seconds;
                        cmd.Parameters.Add("ModifiedDate2", DbType.DateTime).Value = d2;
                        using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                        {
                            da.Fill(tblRet);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ret = new Exception(ex.Message, ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return ret;
        }
        #endregion Data
        #region SP
        public static Exception Verify_WSSList_ShopSuplier(string siteStr)
        {
            Exception ret = null;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteStr))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        try
                        {
                            SPList list = web.Lists["Поставщики"];
                        }
                        catch (Exception err)
                        {
                            ret = new Exception(err.Message, err);
                        }
                    }
                }
            });
            return ret;
        }
        public static Exception IsExist(ref bool parIsExist, string siteStr, string AxID)
        {
            string Name = "";
            return IsExist(ref parIsExist, ref Name, siteStr, AxID);
        }
        public static Exception IsExist(ref bool IsExist, ref string parName, string siteStr, string AxID)
        {
            Exception ret = null;
            string Name = "";
            bool found = false;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteStr))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        try
                        {
                            SPList list = web.Lists["Поставщики"];
                            SPQuery q = new SPQuery();
                            q.Query = @"
   <Where>
      <Eq>
         <FieldRef Name='AxID' />
         <Value Type='Text'>" + AxID + @"</Value>
      </Eq>
   </Where>
";
                            q.RowLimit = 2;
                            SPListItemCollection items = list.GetItems(q);
                            if (items.Count > 0)
                            {
                                Name = items[0]["Название"].ToString();
                                found = true;
                            }
                        }
                        catch (Exception err)
                        {
                            ret = new Exception(err.Message, err);
                        }
                    }
                }
            });
            parName = Name;
            IsExist = found;
            return ret;
        }
        public static Exception Insert(string siteStr, string Title, string AxID, string oneC_Id, string FullName)
        {
            Exception ret = null;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteStr))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        try
                        {
                            SPList splist = web.Lists["Поставщики"];
                            SPListItem item = splist.AddItem();
                            item["Title"] = Title;
                            item["AxID"] = AxID;
                            item["1CID"] = oneC_Id;
                            item["FullName"] = FullName;
                            item.Update();
                            item.Update();
                        }
                        catch (Exception err)
                        {
                            ret = new Exception(err.Message, err);
                        }
                    }
                }
            });
            return ret;
        }
        #endregion

    }

}
